﻿using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;


namespace Future.DataAccess
{
    public class SqlDBOperator : IDBOperator
    {
        private static SqlDBOperator _Instance;
        public static SqlDBOperator shareSQLOperater()
        {
            if (_Instance == null)
            {
                _Instance = new SqlDBOperator();
            }
            return _Instance;
        }

        private IDbConnection Connect;
        private IDbTransaction Tran;
        private string _connectstring = "Server=220.162.247.147,9988;DataBase=Azku;Uid=sa;Pwd=CH2010dns";

        public string ConnectString
        {
            get { return _connectstring; }
            set { _connectstring = value; }
        }

        #region IDBOperator 成员

        public void OpenConnection()
        {
            if (Connect == null)
            {
                if (string.IsNullOrEmpty(ConnectString))
                {
                    throw new Exception("数据库链接字符串未配置!");
                }
                Connect = new SqlConnection(ConnectString);
            }
            if (Connect.State == ConnectionState.Closed)
            {
                try
                {
                    Connect.Open();
                }
                catch
                {
                    throw new Exception("数据库连接失败，请查看数据库连接是否正确！");
                }
            }
        }

        public void CloseConnection()
        {
            if (Connect != null)
            {
                if (Connect.State == ConnectionState.Open)
                {
                    Connect.Close();
                }
            }
        }

        public void BeginTransaction()
        {
            if (Tran == null)
            {
                OpenConnection();
                Tran = Connect.BeginTransaction();
            }
            else
            {
                throw new Exception("事务已开始，必须先提交或回滚后才可打开新的事务.");
            }
        }

        public void CommitTransaction()
        {
            if (Tran != null)
            {
                Tran.Commit();
                CloseConnection();
                Tran = null;
            }
            else
            {
                throw new Exception("事务未开始，不能提交事务.");
            }
        }

        public void RollbackTransaction()
        {
            if (Tran != null)
            {
                Tran.Rollback();
                CloseConnection();
                Tran = null;
            }
            else
            {
                throw new Exception("事务未开始，不能回滚事务.");
            }
        }

        public IDataParameter CreateParameter(string strParaName, object objParaValue, DbType Dbtype)
        {
            SqlParameter para = new SqlParameter();
            para.ParameterName = "@" + strParaName;
            para.Value = objParaValue;
            para.DbType = Dbtype;
            return para;
        }

        public IDataParameter CreateParameter(string strParaName, object objParaValue, DbType Dbtype, int intParaSize)
        {
            IDataParameter para = CreateParameter(strParaName, objParaValue, Dbtype);
            ((SqlParameter)para).Size = intParaSize;
            return para;
        }

        public IDataParameter CreateParameter(string strParaName, object objParaValue, DbType Dbtype, ParameterDirection Paradirect)
        {
            IDataParameter para = CreateParameter(strParaName, objParaValue, Dbtype);
            para.Direction = Paradirect;
            return para;
        }

        public IDataParameter CreateParameter(string strParaName, object objParaValue, DbType Dbtype, int intParaSize, ParameterDirection Paradirect)
        {
            IDataParameter para = CreateParameter(strParaName, objParaValue, Dbtype, Paradirect);
            ((SqlParameter)para).Size = intParaSize;
            return para;
        }

        public IDataParameter CreateParameter(string strParaName, object objParaValue, DbType Dbtype, int intPrecision, int intScale, ParameterDirection Paradirect)
        {
            IDataParameter para = CreateParameter(strParaName, objParaValue, Dbtype, Paradirect);
            ((SqlParameter)para).Precision = (byte)intPrecision;
            ((SqlParameter)para).Scale = (byte)intScale;
            return para;
        }

        public DataSet GetDataSet(string strSql)
        {
            bool conflag = false;
            DataSet ds = new DataSet();
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strSql, CommandType.Text);
                IDbDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;
                adapter.Fill(ds);

                if (conflag == true)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw new Exception(ex.Message);
            }
            return ds;
        }

        public DataSet GetDataSet(string strSql, IDataParameter[] para)
        {
            bool conflag = false;
            DataSet ds = new DataSet();
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strSql, CommandType.Text);
                SetParaments(cmd, para);
                IDbDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;

                adapter.Fill(ds);

                if (conflag == true)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw ex;
            }
            return ds;
        }

        public DataTable GetDataTable(string strSql)
        {
            return GetDataSet(strSql).Tables[0];
        }

        public DataTable GetDataTable(string strSql, IDataParameter[] para)
        {
            return GetDataSet(strSql, para).Tables[0];
        }

        public object GetDataSingle(string strSql)
        {
            bool conflag = false;
            object objvalue = new object();
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strSql, CommandType.Text);
                objvalue = cmd.ExecuteScalar();
                if (conflag == true)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw ex;
            }
            return objvalue;
        }

        public object GetDataSingle(string strSql, IDataParameter[] para)
        {
            bool conflag = false;
            object objvalue = new object();
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strSql, CommandType.Text);
                SetParaments(cmd, para);
                objvalue = cmd.ExecuteScalar();
                if (conflag == true)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw ex;
            }
            return objvalue;
        }

        public int ExecuteSql(string strSql)
        {
            bool conflag = false;
            int intcount = -1;
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strSql, CommandType.Text);
                intcount = cmd.ExecuteNonQuery();

                if (conflag == true)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw ex;
            }
            return intcount;
        }

        public int ExecuteSql(string strSql, IDataParameter[] para)
        {
            bool conflag = false;
            int intcount = -1;
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strSql, CommandType.Text);
                SetParaments(cmd, para);
                intcount = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                if (conflag == true)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw ex;
            }
            return intcount;
        }

        public void ExecuteProc(string strProcName, IDataParameter[] para)
        {
            ExecuteProc(strProcName, ref para);
        }

        public void ExecuteProc(string strProcName, ref  IDataParameter[] para)
        {
            bool conflag = false;
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strProcName, CommandType.StoredProcedure);
                SetParaments(cmd, para);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();

                if (conflag == true)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw ex;
            }
        }

        public DataTable ExecuteProcTable(string strProcName)
        {
            bool conflag = false;
            DataSet ds = new DataSet();
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strProcName, CommandType.StoredProcedure);

                IDbDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;

                adapter.Fill(ds);

                if (conflag == true)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw ex;
            }
            return ds.Tables[0];
        }

        public DataTable ExecuteProcTable(string strProcName, IDataParameter[] para)
        {
            bool conflag = false;
            DataSet ds = new DataSet();
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strProcName, CommandType.StoredProcedure);
                SetParaments(cmd, para);
                IDbDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = cmd;

                adapter.Fill(ds);

                if (conflag == true)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw ex;
            }
            return ds.Tables[0];
        }

        public IDataReader GetReader(string strSql)
        {
            bool conflag = false;
            IDataReader dataReader = null;
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strSql, CommandType.Text);
                dataReader = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw ex;
            }
            return dataReader;
        }
        public IDataReader GetReader(string strSql, IDataParameter[] para)
        {
            bool conflag = false;
            IDataReader dataReader = null;
            try
            {
                conflag = CheckFunConnection();

                IDbCommand cmd = CreateCommand(strSql, CommandType.Text);
                SetParaments(cmd, para);
                dataReader = cmd.ExecuteReader();
            }
            catch (Exception ex)
            {
                if (conflag == true)
                {
                    CloseConnection();
                }
                throw ex;
            }
            return dataReader;
        }

        #endregion

        private void SetParaments(IDbCommand cmd, IDataParameter[] cmdParms)
        {
            if (cmdParms != null)
            {
                foreach (IDataParameter parm in cmdParms)
                {
                    SetParamentFormat(parm);
                    cmd.Parameters.Add(parm);
                }
            }
        }

        private void SetParamentFormat(IDataParameter parm)
        {
            if (parm.DbType == DbType.Binary)
            {
                ((SqlParameter)parm).SqlDbType = SqlDbType.Image;
            }
            if (parm.DbType == DbType.String)
            {
                ((SqlParameter)parm).SqlDbType = SqlDbType.Text;
            }
            if (parm.DbType == DbType.AnsiString)
            {
                ((SqlParameter)parm).SqlDbType = SqlDbType.VarChar;
            }
            if (parm.DbType == DbType.Decimal)
            {
                ((SqlParameter)parm).SqlDbType = SqlDbType.Decimal;
            }
        }

        private bool CheckFunConnection()
        {
            if (Connect == null || Connect.State == ConnectionState.Closed)
            {
                OpenConnection();
                return true;
            }
            else
            {
                return false;
            }
        }

        private IDbCommand CreateCommand(string strSql, CommandType CmdType)
        {
            IDbCommand cmd = new SqlCommand();
            cmd.CommandText = strSql;
            cmd.CommandType = CmdType;
            cmd.Connection = Connect;
            if (Tran != null)
            {
                cmd.Transaction = Tran;
            }
            return cmd;
        }
    }
}
